<?php
require_once(CORE_DIR.'Common.php');
require_once(CORE_DIR.'yammerModel.php');
require_once(CORE_DIR.'taskModel.php');
require_once(CORE_DIR.'userModel.php');
require_once(CORE_DIR.'mappers/ProjectMapper.php');


define("DEFAULT_PROJECT", "UNSORTED");

class projectModel extends Model{

	function __construct(){
		$this->conn = $this->getConnection();
	}
	
	function addProject($prjName){
		$newOrderId = $this->getNewProjectOrder();
		
		$query = "insert into project (name, creationDate, order_id)
				  values ('%s', now(), %d)";
		$query = $this->format($query, array($prjName, $newOrderId));
		
		$this->execute($query, null);
		return mysqli_insert_id($this->conn);
	}
	
	function updateProject($id, $prjName, $projectOrderList){
		$this->updateProjectOrder($projectOrderList);
		
		$query = "update project set name='%s' where id=%d";
		$query = $this->format($query, array($prjName, $id));		
		return $this->execute($query, null);		
	}	
	
	function updateProjectArchState($id, $archState){
		$query = "update project set archstate=%d, archStateChangeDate=now() where id=%d";
		$query = $this->format($query, array($archState, $id));		
		return $this->execute($query, null);	
	}	
		
	function deleteProject($id){
		$query = "delete from project where id=%d";
		$query = $this->format($query, array($id));		
		if($this->execute($query, null))
			if($this->deleteTasksOfProject($id))
				return $this->deleteAllUnprojectTasks();
		return false;		
	}		
	
	function getProjects(){
		$query = "SELECT * FROM project WHERE archstate=0 ORDER BY order_id";
		return $this->execute($query, new ProjectMapper());				
	}
	
	function getDefaultProject(){
		$query = "SELECT * FROM project WHERE name='".DEFAULT_PROJECT."' ";
		$prjDtoList = $this->execute($query, new ProjectMapper());				
		if($prjDtoList[0] != null)
		{
			return $prjDtoList[0]->getId();
		}
		else{
			return $this->addProject(DEFAULT_PROJECT);
		}
	}	
	
	function getPermittedProjects($userId)
	{
		$query = "SELECT * FROM project WHERE archstate = 0 AND id IN (SELECT projectID FROM permissions WHERE userID = %d) ORDER BY order_id";
		$query = $this->format($query, array($userId));
		return $this->execute($query, new ProjectMapper());				
	}

	function getArchivedProjects(){
		$query = "SELECT * FROM project WHERE archstate=1 ORDER BY creationDate DESC";		
		return $this->execute($query, new ProjectMapper());				
	}
	
	function getUserProjects($userid){
		$query = "SELECT distinct p.* FROM project p 
				  LEFT JOIN task t ON t.projectId = p.id 
				  LEFT JOIN taskuser tu ON t.id = tu.taskId
				  WHERE p.archstate=0 and tu.userId = %d
				  ORDER BY p.creationDate DESC";
		$query = $this->format($query, array($userid));
		return $this->execute($query, new ProjectMapper());				
	}
	
	function deleteTasksOfProject($prjId){
		$query = "delete from projecttask where projectId=%d";
		$query = $this->format($query, array($prjId));		
		return $this->execute($query, null);				
	}

	function deleteAllUnprojectTasks() {
		$query = "delete from task where projectid is null";
		return $this->execute($query, null);				
	}
	
	function reportUpdates($projectId) {
		
		$userList = $this->getProjectRelatedUserListUnreported($projectId);
		
		foreach ($userList as $user) {
			if(!$this->sendReportEmail($user['id'], $projectId))
				return false;
		}

		$this->makeProjectReported($projectId);
		return true;
	}

	function getProjectRelatedUserList($projectId){
		$query = "select distinct u.* from task t 
				left join taskuser tu on t.id=tu.taskId 
				left join u.id = t.author or tu.userId = u.id 
				where t.projectId = %d and t.deleted=0";
		$result = mysqli_query($this->conn, $this->format($query, array($projectId)));
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['firstname'] = $row['firstName'];
			$list[$row['id']]['lastname'] = $row['lastName'];
			$list[$row['id']]['nickname'] = $row['nickName'];
			$list[$row['id']]['email'] = $row['email'];
		}
 		mysqli_free_result($result);
		return $list;		
	}
	
	function getProjectRelatedUserListUnreported($projectId){
		$query = "select distinct u.* from task t 
				left join taskuser tu on t.id=tu.taskId 
				left join user u on u.id = t.author or tu.userId = u.id 
				where t.reported=0 and t.status=0 and t.projectId = %d and t.deleted=0";
		$result = mysqli_query($this->conn, $this->format($query, array($projectId)));
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']]['id'] = $row['id'];
			$list[$row['id']]['firstname'] = $row['firstName'];
			$list[$row['id']]['lastname'] = $row['lastName'];
			$list[$row['id']]['nickname'] = $row['nickName'];
			$list[$row['id']]['email'] = $row['email'];
		}
 		mysqli_free_result($result);
		return $list;		
	}
	
	function sendReportEmail($userId, $projectId) {
		$uModel = new userModel();
		$yModel = new yammerModel();
		$tModel = new taskModel();
		$uDto = $uModel->getUserData($userId);


		$countNew = $tModel->getUnreportedTasksCount($userId, $projectId, 0);
		$countUpdated = $tModel->getUnreportedTasksCount($userId, $projectId, 1);
		$subject = ($countNew > 0) ? "NEW TASKS - ".$countNew : "";		
		$subject .= ($countUpdated > 0) ? (($countNew > 0) ? " | " : "")."UPDATED TASKS - ".$countUpdated : "";
		
		$params['firstname'] = $uDto->getFirstName();
		$params['lastname'] = $uDto->getLastName();
		$params['querystr'] = "pid=".$projectId."&uid=".$userId;
		$params['_BESTNAME_'] = $uDto->getNickName();
		$params['_NEWTASKS_'] = $countNew;
		$params['_UPDATEDTASKS_'] = $countUpdated;
		$params['_UPDATED_TASK_REPORT_SUBJECT_'] = $subject;
		
//		$yModel->incYammerDirectMessage($userId, "yammerupdatetask", $params);
		return incSendMail($uDto->getEmail(), "reportupdates", $params);
	}
	
	function getProjectByID($projectId) {
		$query = "SELECT * FROM project WHERE id =%d";		
		$result = mysqli_query($this->conn, $this->format($query, array($projectId)));				
		
		$list = array();
		while($row = mysqli_fetch_assoc($result))
		{
			$list[$row['id']] = $row;			
		}
 		mysqli_free_result($result);
		return $list;		

	}
	
	function makeProjectReported($projectId) {
		$query = "update taskuser set status = 1 where taskId in (select id from task where projectId = %d and reported=0 and deleted=0)";
		$updateUserTasks = $this->execute($this->format($query, array($projectId)), null);
		$query = "UPDATE task SET reported=1 WHERE projectId =%d";
		$updatePrj = $this->execute($this->format($query, array($projectId)), null);
		return $updatePrj && $updateUserTasks;
	}
	
	function getNewProjectOrder(){		
		$query = "SELECT MAX(order_id)+1 as new_order_id FROM project WHERE archstate = 0";
		$result = mysqli_query($this->conn, $query);	
		$row = mysqli_fetch_assoc($result);
 		mysqli_free_result($result);
		return $row['new_order_id'];
	}
	
	function updateProjectOrder($orderList){
		for ($i = 0; $i < count($orderList); $i++){
			$query = "update project set order_id = %d where id = %d";
			$query = $this->format($query, array($i+1, $orderList[$i]));
			$res = $this->execute($query, null);				
		}
	}
}
?>
